﻿using System;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.Web;
using System.Data.Objects;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;

namespace SSCCMembership.Web.Services.Members
{
    [ErrorHandlerBehavior]
    [ServiceContract(Namespace = "")]
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    public class UserHome
    {

        [OperationContract]
        public BasicMember[] GetBasicMembers()
        {
            //Random R = new Random();
            Query UserMembersQ;
            if (SecurityTester.TryAssertRoles("EditAnybody"))
            {
                UserMembersQ = new Query("SELECT * FROM SSCC_DAT_Member", new Dictionary<SqlParameter, object>());
            }
            else
            {
                UserMembersQ = new Query(
                    "SELECT m.* FROM SSCC_DAT_Family AS f INNER JOIN SSCC_DAT_Member AS m ON f.Member = m.ID WHERE (f.UserID = @UID)",
                    new Dictionary<SqlParameter, object>() { 
                        { new SqlParameter("@UID", SqlDbType.VarChar, 255), HttpContext.Current.User.Identity.Name } 
                    }
                    );
            }
            int CurrentYear;
            using (var Q = new Query(
                "SELECT ID FROM SSCC_DAT_Year WHERE StartDate<@NOW AND EndDate>@NOW", 
                new Dictionary<SqlParameter, object>() 
                { 
                    {new SqlParameter("@NOW", SqlDbType.Date), DateTime.Now }
                }
                ))
            {
                CurrentYear = (int)Q.First()["ID"];
            }
            var MembersQ = UserMembersQ.Select((UM) => 
            {
                var BM = new BasicMember()
                {
                    ID = (int)UM["ID"],
                    FullName = String.Format("{0} {1}", UM["FirstName"], UM["Surname"])
                };

                int MembershipID = 0;
                using (var Q = new Query(
                    "SELECT m.RenewChecked, m.ID FROM SSCC_DAT_Membership as m WHERE m.Member=@MemberID AND m.Year=@YearID",
                    new Dictionary<SqlParameter, object>()
                    {
                        {new SqlParameter("@MemberID", SqlDbType.Int), UM["ID"]},
                        {new SqlParameter("@YearID", SqlDbType.Int), CurrentYear}
                    }))
                {
                    var Memb = Q.First();
                    BM.RenewChecked = Memb["RenewChecked"].Nullable<bool?>();
                    BM.UpdatedDetails = BM.RenewChecked.HasValue;
                    MembershipID = (int)Memb["ID"];
                }
                using (var Q = new Query(
                    "SELECT RA.ID, RA.Renew, R.RackNumber, RA.BoatTypeRC FROM SSCC_DAT_RackAllocation AS RA INNER JOIN SSCC_DAT_Rack AS R ON RA.Rack = R.ID WHERE (RA.Membership = @MembershipID)",
                    new Dictionary<SqlParameter, object>()
                    {
                        {new SqlParameter("@MembershipID", SqlDbType.Int), MembershipID}
                    }))
                {
                    BM.Racks = Q.Select((R) => new BasicRackAllocation()
                    {
                        ID = (int)R["ID"],
                        RenewChecked = R["Renew"].Nullable<bool?>(),
                        Name = String.Format("{0} - {1}", R["RackNumber"], ((int)R["BoatTypeRC"]).GetRefCodeDescription())
                    }).ToArray();
                }

                return BM;
            }).ToArray();

            return MembersQ;
        }

        [OperationContract]
        public void SetRenewCheckedForMember(int ID, bool? RenewChecked)
        {
            string statement =
                "UPDATE       SSCC_DAT_Membership " +
                "SET                RenewChecked = @RenewChecked " +
                "FROM            SSCC_DAT_Membership INNER JOIN " +
                "                         SSCC_DAT_Member ON SSCC_DAT_Membership.Member = SSCC_DAT_Member.ID INNER JOIN " +
                "                         SSCC_DAT_Year ON SSCC_DAT_Membership.Year = SSCC_DAT_Year.ID " +
                "WHERE        (SSCC_DAT_Member.ID = @ID) AND (SSCC_DAT_Year.StartDate < @NOW) AND (SSCC_DAT_Year.EndDate > @NOW)";

            SSCCDatabase.ExecuteStatement(statement, new Params()
            {
                {new SqlParameter("@RenewChecked", SqlDbType.Bit), RenewChecked.ToDBNullable()},
                {new SqlParameter("@ID", SqlDbType.Int), ID},
                {new SqlParameter("@NOW", SqlDbType.Date), DateTime.Now}
            });
        }
        [OperationContract]
        public void SetRenewCheckedForRackAllocation(int ID, bool? RenewChecked)
        {
            string statement =
                "UPDATE       SSCC_DAT_RackAllocation " +
                "SET                Renew = @RenewChecked " +
                "WHERE        (ID = @ID)";
            SSCCDatabase.ExecuteStatement(statement, new Params()
            {
                {new SqlParameter("@RenewChecked", SqlDbType.Bit), RenewChecked.ToDBNullable()},
                {new SqlParameter("@ID", SqlDbType.Int), ID}
            });
        }
    }
    [DataContract]
    public class BasicMember
    {
        [DataMember]
        public int ID { get; set; }
        [DataMember]
        public string FullName { get; set; }
        [DataMember]
        public bool UpdatedDetails { get; set; }
        [DataMember]
        public bool? RenewChecked { get; set; }
        [DataMember]
        public BasicRackAllocation[] Racks { get; set; }
    }
    [DataContract]
    public class BasicRackAllocation
    {
        [DataMember]
        public int ID { get; set; }
        [DataMember]
        public string Name { get; set; }
        [DataMember]
        public bool? RenewChecked { get; set; }
    }
}
